﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Collections;

namespace iHub
{
    class PayrollManager : dbConnector
    {
        private SqlDataReader read;
        private SqlCommand com;

        public void CreateVoucher(ArrayList assigns, int x, int casha)
        {
            ArrayList assignments = assigns;
            int id = x;
            float gross = 0;
            float net = 0;
            float advances = 0;
            int cash = casha;
            float tax = (float)0.02;
            int pid = 0;
            String code = "WC120";
            DateTime date = DateTime.Now;
            String d = date.ToShortDateString();

            try
            {
                connect();
                beginTransaction();
                com = new SqlCommand();
                com.Connection = getCon();
                com.Transaction = getTransaction();

                for (int i = 0; i < assignments.Count; i++)
                {
                    com.CommandText = "select payment from Assignment_Type where type = (select type from Assignment where assign_id = @assign)";
                    com.CommandType = CommandType.Text;
                    com.Parameters.AddWithValue("@assign", assignments[i]);
                    read = com.ExecuteReader();

                    if (read.Read())
                    {
                        gross += Convert.ToInt32(read["payment"]);
                    }

                    read.Close();
                    com.Parameters.Clear();
                }

                advances = gross * tax;
                net = gross - (advances + cash);

                com.CommandText = "dbo.createPay";
                com.CommandType = CommandType.StoredProcedure;

                com.Parameters.Add(new SqlParameter("e_id", id));
                com.Parameters.Add(new SqlParameter("gross", gross));
                com.Parameters.Add(new SqlParameter("cash", cash));
                com.Parameters.Add(new SqlParameter("tax", advances));
                com.Parameters.Add(new SqlParameter("net", net));
                com.Parameters.Add(new SqlParameter("code", code));
                com.Parameters.Add(new SqlParameter("date", d));

                com.ExecuteNonQuery();
                com.Parameters.Clear();

                com.CommandText = "select p_id from Payroll where e_id = @id and p_date = @d";
                com.CommandType = CommandType.Text;
                com.Parameters.AddWithValue("@id", id);
                com.Parameters.AddWithValue("@d", d);
                read = com.ExecuteReader();

                if (read.Read())
                {
                    pid = Convert.ToInt32(read["p_id"]);
                }

                com.Parameters.Clear();
                read.Close();

                for (int i = 0; i < assignments.Count; i++)
                {
                    com.CommandText = "insert into Payroll_Assignment (p_id, assign_id) values (@pid, @assignid)";
                    com.CommandType = CommandType.Text;
                    com.Parameters.Add(new SqlParameter("pid", pid));
                    com.Parameters.Add(new SqlParameter("assignid", Convert.ToInt32(assignments[i])));

                    com.ExecuteNonQuery();
                    com.Parameters.Clear();
                }

                commit();
                disconnect();
            }
            catch (Exception err)
            {
                rollback();
                disconnect();
                throw new Exception(err.ToString());
            }
        }

        public bool checkPay(int eid)
        {
            DateTime d = DateTime.Now;
            DateTime d2 = new DateTime();
            int id = eid;
            int span = 0;
            bool check = false;

            try
            {
                connect();

                com = new SqlCommand("select top 1 p_date from Payroll where e_id = " + id + " order by p_date desc", getCon());
                read = com.ExecuteReader();

                if (read.Read())
                {
                    d2 = Convert.ToDateTime(read["p_date"]);
                }

                if (d2 == null)
                {
                    check = true;
                }
                else if (d2 != null)
                {
                    span = (int)(d - d2).TotalDays;
                }

                if (span > 6)
                {
                    check = true;
                }
                else
                {
                    check = false;
                }

                disconnect();
            }
            catch (Exception err)
            {
                disconnect();
                throw new Exception(err.ToString());
            }

            return check;
        }

        public DataTable viewVoucher(int i, DateTime date)
        {
            DataTable vouchers = new DataTable();
            String d = date.ToShortDateString();
            int id = i;
            String name = "";
            String tin = "";
            String address = "";
            float gross1 = 0;
            float net1=0;
            float cash=0;
            float tax = 0;
            double tax3 = 0;
            double net3 = 0;
            int pid = 0;
            String tax1 = "";
            int pay = 0;
            String location = "";
            String net = "";
            int batch = 0;
            ArrayList l = new ArrayList();

            try
            {
                connect();

                vouchers.Columns.Add(new DataColumn("Date", typeof(string)));
                vouchers.Columns.Add(new DataColumn("Name", typeof(string)));
                vouchers.Columns.Add(new DataColumn("Tax Identification Number", typeof(string)));
                vouchers.Columns.Add(new DataColumn("Employee Address", typeof(string)));
                vouchers.Columns.Add(new DataColumn("Assignment Location", typeof(string)));
                vouchers.Columns.Add(new DataColumn("Assignment Pay", typeof(string)));
                vouchers.Columns.Add(new DataColumn("Batch No.", typeof(int)));
                vouchers.Columns.Add(new DataColumn("Gross Amount Payable", typeof(float)));
                vouchers.Columns.Add(new DataColumn("Cash Advances", typeof(float)));
                vouchers.Columns.Add(new DataColumn("Withholding Tax", typeof(float)));
                vouchers.Columns.Add(new DataColumn("Net Amount Payable", typeof(float)));

                com = new SqlCommand("select Employee.e_name as name, Employee.tin as tin, Employee.e_city as address, Payroll.* from Payroll inner join Employee on Employee.e_id=Payroll.e_id where Payroll.e_id = " + id + " and p_date = '" + d + "'", getCon());
                read = com.ExecuteReader();

                if (read.Read())
                {
                    name = Convert.ToString(read["name"]);
                    tin = Convert.ToString(read["tin"]);
                    address = Convert.ToString(read["address"]);
                    pid = Convert.ToInt32(read["p_id"]);
                    cash = Convert.ToInt32(read["cashadvances"]);
                    gross1 = Convert.ToInt32(read["grossamountpayable"]);
                    tax1 = Convert.ToString(read["withholdingtax"]);
                    tax3 = Convert.ToDouble(tax1);
                    tax = (float)tax3;
                    net = Convert.ToString(read["netamountpayable"]);
                    net3 = Convert.ToDouble(net);
                    net1 = (float)net3;
                }

                read.Close();

                com = new SqlCommand("select Assignment.batchno as batch, Assignment.city as loc, Assignment_Type.payment as pay from (Assignment inner join Payroll_Assignment on Assignment.assign_id=Payroll_Assignment.assign_id) inner join Assignment_Type on Assignment.type=Assignment_Type.type where Payroll_Assignment.p_id = " + pid, getCon());
                read = com.ExecuteReader();

                while (read.Read())
                {
                    location = Convert.ToString(read["loc"]);
                    pay = Convert.ToInt32(read["pay"]);
                    batch = Convert.ToInt32(read["batch"]);
                    vouchers.Rows.Add(d, name, tin, address, location, pay, batch, gross1, cash, tax, net1);
                }

                read.Close();

                disconnect();
            }
            catch (Exception err)
            {
                disconnect();
                throw new Exception(err.ToString());
            }

            return vouchers;
        }

        public DataTable viewList(DateTime a, DateTime b)
        {
            DataTable list = new DataTable();
            string name = "";
            String temptin = "";
            string gross = "";
            double gross3 = 0;
            float gross2 = 0;
            string cash = "";
            double cash3 = 0;
            float cash2 = 0;
            string tax = "";
            double tax3 = 0;
            float tax2 = 0;
            string net = "";
            double net3 = 0;
            float net2 = 0;

            try
            {
                connect();

                list.Columns.Add(new DataColumn("Name", typeof(string)));
                list.Columns.Add(new DataColumn("Tax Identification Number", typeof(string)));
                list.Columns.Add(new DataColumn("Gross Amount Payable", typeof(float)));
                list.Columns.Add(new DataColumn("Cash Advances", typeof(float)));
                list.Columns.Add(new DataColumn("Withholding Tax", typeof(float)));
                list.Columns.Add(new DataColumn("Net Amount Payable", typeof(float)));

                com = new SqlCommand("select Employee.e_name as name, Employee.tin as tin, Payroll.* from Payroll inner join Employee on Employee.e_id=Payroll.e_id where p_date between '" + a + "' and '" + b + "'", getCon());
                read = com.ExecuteReader();

                while (read.Read())
                {
                    name = (string)read["name"];
                    temptin = (string)read["tin"];
                    gross = Convert.ToString(read["grossamountpayable"]);
                    gross3 = Convert.ToDouble(gross);
                    gross2 = (float)gross3;
                    cash = Convert.ToString(read["cashadvances"]);
                    cash3 = Convert.ToDouble(cash);
                    cash2 = (float)cash3;
                    tax = Convert.ToString(read["withholdingtax"]);
                    tax3 = Convert.ToDouble(tax);
                    tax2 = (float)tax3;
                    net = Convert.ToString(read["netamountpayable"]);
                    net3 = Convert.ToDouble(net);
                    net2 = (float)net3;

                    list.Rows.Add(name, temptin, gross2, cash2, tax2, net2);
                }

                read.Close();

                disconnect();
            }
            catch (Exception err)
            {
                disconnect();
                throw new Exception(err.ToString());
            }

            return list;
        }

        public DataTable viewBIR(int month,int year)
        {
            int m = month;
            String m2 = "";
            DataTable list = new DataTable();
            string name = "";
            String temptin = "";
            string gross = "";
            double gross3 = 0;
            float gross2 = 0;
            string tax = "";
            double tax3 = 0;
            float tax2 = 0;
            string net = "";
            double net3 = 0;
            float net2 = 0;
            string code = "";
            string services = "Contractual Services";

            if (m == 1)
            {
                m2 = "January";
            }
            else if (m == 2)
            {
                m2 = "February";
            }
            else if (m == 3)
            {
                m2 = "March";
            }
            else if (m == 4)
            {
                m2 = "April";
            }
            else if (m == 5)
            {
                m2 = "May";
            }
            else if (m == 6)
            {
                m2 = "June";
            }
            else if (m == 7)
            {
                m2 = "July";
            }
            else if (m == 8)
            {
                m2 = "August";
            }
            else if (m == 9)
            {
                m2 = "September";
            }
            else if (m == 10)
            {
                m2 = "October";
            }
            else if (m == 11)
            {
                m2 = "November";
            }
            else if (m == 12)
            {
                m2 = "December";
            }

            try
            {
                connect();

                list.Columns.Add(new DataColumn("Month", typeof(string)));
                list.Columns.Add(new DataColumn("Tax Identification Number", typeof(string)));
                list.Columns.Add(new DataColumn("Name", typeof(string)));
                list.Columns.Add(new DataColumn("ATC Code", typeof(string)));
                list.Columns.Add(new DataColumn("Nature of Payment", typeof(string)));
                list.Columns.Add(new DataColumn("Gross Amount Payable", typeof(float)));
                list.Columns.Add(new DataColumn("Withholding Tax", typeof(float)));
                list.Columns.Add(new DataColumn("Net Amount Payable", typeof(float)));

                com = new SqlCommand("select Employee.e_name as name, Employee.tin as tin, Payroll.* from Payroll inner join Employee on Employee.e_id=Payroll.e_id where month(p_date) = @month and year(p_date) = @year", getCon());
                com.Parameters.AddWithValue("@month", m);
                com.Parameters.AddWithValue("@year", year);
                read = com.ExecuteReader();

                while (read.Read())
                {
                    name = (string)read["name"];
                    temptin = (string)read["tin"];
                    code = Convert.ToString(read["atc_code"]);
                    gross = Convert.ToString(read["grossamountpayable"]);
                    gross3 = Convert.ToDouble(gross);
                    gross2 = (float)gross3;
                    tax = Convert.ToString(read["withholdingtax"]);
                    tax3 = Convert.ToDouble(tax);
                    tax2 = (float)tax3;
                    net = Convert.ToString(read["netamountpayable"]);
                    net3 = Convert.ToDouble(net);
                    net2 = (float)net3;

                    list.Rows.Add(m2, temptin, name, code, services, gross2, tax2, net2);
                }

                read.Close();

                disconnect();
            }
            catch (Exception err)
            {
                disconnect();
                throw new Exception(err.ToString());
            }

            return list;
        }

        public DataTable viewBIR(int year)
        {
            String m2 = "";
            DataTable list = new DataTable();
            string name = "";
            String temptin = "";
            string gross = "";
            double gross3 = 0;
            float gross2 = 0;
            string tax = "";
            double tax3 = 0;
            float tax2 = 0;
            string net = "";
            double net3 = 0;
            float net2 = 0;
            string code = "";
            string services = "Contractual Services";

            try
            {
                connect();

                list.Columns.Add(new DataColumn("Month", typeof(string)));
                list.Columns.Add(new DataColumn("Tax Identification Number", typeof(string)));
                list.Columns.Add(new DataColumn("Name", typeof(string)));
                list.Columns.Add(new DataColumn("ATC Code", typeof(string)));
                list.Columns.Add(new DataColumn("Nature of Payment", typeof(string)));
                list.Columns.Add(new DataColumn("Gross Amount Payable", typeof(float)));
                list.Columns.Add(new DataColumn("Withholding Tax", typeof(float)));
                list.Columns.Add(new DataColumn("Net Amount Payable", typeof(float)));

                com = new SqlCommand("select Employee.e_name as name, Employee.tin as tin, datename(month, Payroll.p_date) as month, Payroll.* from Payroll inner join Employee on Employee.e_id=Payroll.e_id where year(Payroll.p_date) = @year order by Payroll.p_date desc", getCon());
                com.Parameters.AddWithValue("@year", year);
                read = com.ExecuteReader();

                while (read.Read())
                {
                    m2 = (string)read["month"];
                    name = (string)read["name"];
                    temptin = (string)read["tin"];
                    code = Convert.ToString(read["atc_code"]);
                    gross = Convert.ToString(read["grossamountpayable"]);
                    gross3 = Convert.ToDouble(gross);
                    gross2 = (float)gross3;
                    tax = Convert.ToString(read["withholdingtax"]);
                    tax3 = Convert.ToDouble(tax);
                    tax2 = (float)tax3;
                    net = Convert.ToString(read["netamountpayable"]);
                    net3 = Convert.ToDouble(net);
                    net2 = (float)net3;

                    list.Rows.Add(m2, temptin, name, code, services, gross2, tax2, net2);
                }

                read.Close();

                disconnect();
            }
            catch (Exception err)
            {
                disconnect();
                throw new Exception(err.ToString());
            }

            return list;
        }
    }
}
